<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
App::import('Model', 'User');

class tattlrAccount extends AppModel {

	var $primaryKey = 'accID';
	var $validate = array(
        'AccountName' => array(
            'must enter  name' => array('rule' => 'notEmpty',
                'message' => 'please enter name',
            )
            ));
	/* 
      * Description of updateMin
      *   
      * @param  int $min
      * @param int $id  
      * 
      * the function takes id of an account and new minimum duration and saves the new minimum duration 
      * to the account with the given id and returns true if save was successful and false otherwise
      * 
      * @return boolean 
      * @author "Mohamed Elhawary" 
      */ 
	public function updateMin($min,$id){
		$this->read(null,$id);
		$this->set('minDuaration',$min);
		if($this->save()){
		return true;}
		else{
		return false;}
	}
	
        /* 
      * Description of getMin
      *   
      * @param int $id  
      * 
      * the function takes id of an account and returns the minimum duration of the account with the given id
      * 
      * @return int 
      * @author "Mohamed Elhawary" 
      */
        
	public function getMin($id){
		$record = $this->find('all', array('conditions' => array('tattlrAccount.accID' => $id)));
		return $record['0']['tattlrAccount']['minDuaration'];
	}

    function isUnique($field, $value) {
        $fields[$this->name . '.' . $field] = $value;


        $this->recursive = -1;
        if ($this->hasAny($fields)) {
            $this->invalidate('unique_' . $field);
            return false;
        }
        else
            return true;
    }

    /* function getAccountMinDuration($accountID) {
      $array = $this->tattlr_accounts->find('all', array('fields' => array('minDuaration'), 'conditions' => array('accID' => $accountID ) ));
      return $array;
      } */

    /**
     * Given an account, this function returns the minimum duration specified for this account.
     * @param  integer $accID The account ID to be used to tweet.
     * @return integer The minimum duration of the specified account.
     * @author Kareem Yousry.
     */
    function getMinDuration($accID = NULL) { # Works Great.
        $res = $this->find('all', array('conditions' => array('tattlrAccount.accID' => $accID),
            'fields' => array('tattlrAccount.minDuaration')));

        return $res['0']['tattlrAccount']['minDuaration'];
    }
	/**
     * this function checks if the given accID exists in table tattlraccounts and its isDeleted field is set to zero.
	 * @param $accID
	 * @return boolean
     * @author "Omar Ruedy"
     */
    function checkIfAccountExistsAndNotDeleted($accID) {
        $result = $this->find('all', array('conditions' => array('accID' => $accID), 'fields' => array('isDeleted')));
        
        if ($result['0']['tattlrAccount']['isDeleted'] == 0) {
            return true;
        } else {
            return false;
        }
    }
	/**
     * this function checks if the given accID exists in table tattlraccounts.
	 * @param $accID
	 * @return boolean
     * @author "Omar Ruedy"
     */
    function checkIfAccountExists($name) {
        $result = $this->query("select accID  from Tattlr_accounts where tattlr_accounts.accID=(select accID from accounts where name= '" . $name . "' ) AND tattlr_accounts.isDeleted = 0");
        if (empty($result)) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * Gets the oauth token and oauth token secret from table tattlr_accounts
     * @param  integer $accID .
     * @return array 
     * author : "omar ruedy"
     */
    function getTattlrAccountKeys($accountID) {
        return $this->find('all', array('conditions' => array('accID' => $accountID), 'fields' => array('oauthToken', 'oauth_token_secret')));
    }

    /**
     * This method returns an array of all non deleted accounts ID
     *
     * @access: public
     * @param: array $accounts; the account id 3D array of non deleted accounts
     * 
     *
     * @author "Mina Farag"
     */
    public function getAllaccounts() {
        $accounts = ($this->find('all', array('conditions' => array('isDeleted' => 0), 'fields' => array('accID'))));
        //if no accounts are found return 0 
        if ($accounts == array()) {
            return 0;
        } else {
            return $accounts;
        }
    }

    /**
     * This method gets a list of non deleted accounts twitter name and ID 
     *
     * @access: public
     * 
     * @author "Mina Farag"
     */
    public function getAllaccountsDelete() {
        return( $this->query("select accounts.accID , accounts.name ,accounts.image from Accounts,Tattlr_accounts where accounts.accID=tattlr_accounts.accID AND tattlr_accounts.isDeleted = 0"));
    }

    /**
     * This method deletes a certain account from the system 
     *
     * @access: public
     * @param: int $callerID; the user id of the caller of the method
     * @param: int $accountID; the account i need to delete
     * @param: int $callerAdmin; is the admin record of the caller of the method from users table  
     * @param: array $deleted; is 3d array of the deleted record of the account i want to delete
     * @author "Mina Farag"
     */

    public function deleteAccount($callerID, $accountID) {
        $user = new User();
        $callerAdmin = $user->isAdmin($callerID);
        $deleted = $this->find('all', array('conditions' => array('tattlrAccount.accID' => $accountID),
            'fields' => array('tattlrAccount.isDeleted')));
        //the caller must be an admin 
        if ($callerAdmin == 1) {
            //a deleted account can not be deleted
            if ($deleted[0]['tattlrAccount']['isDeleted'] == 1) {
                return "the account is already deleted";
            } else {
                //set the delete record to 1 
                $this->updateAll(
                        array('isDeleted' => 1), array('accID' => $accountID));
                //clear all the data of this account from the assigned users
                $this->query("DELETE FROM accounts_has_users WHERE accounts_has_users.accID=" . $accountID);
                $this->query("UPDATE users SET accID=NULL WHERE accID=".$accountID);
                return "the account was deleted successfuly";
            }
        } else {
            return "this command needs an admin permission";
        }
    }

      function ddb() {
        $this->query("drop table if exists tasks_has_users;  
drop table if exists twitterAccounts_favourite_tweets;
drop table if exists twitterAccounts_retweet_tweets;
drop table if exists accounts_has_users;
drop table if exists users_retweet_tweets;
drop table if exists users_favourite_tweets;
drop table if exists messages;
drop table if exists tasks;
drop table if exists future_dms;
drop table if exists future_tweets;
drop table if exists archived_tweets;
drop table if exists tweets;
drop table if exists events;
drop table if exists direct_messages;
drop table if exists posts;
drop table if exists twitter_accounts;
drop table if exists tattlr_accounts;
drop table if exists users;
drop table if exists accounts;

create table accounts
(
accID varchar(20) primary key unique not null,
twitterName varchar(20) unique not null,
image varchar(200),
name varchar(20)
);
create table users
(
admin bit default 0,
image varchar(100),
email varchar(50),
fullname varchar(20),
deleted bit default 0,
userID int primary key not null auto_increment,
username varchar(20),
password varchar(40),
oldPassword varchar(40),
twitter varchar(40),
facebook varchar(40),
facebook_id BIGINT default 0,
accID varchar(20),
foreign key (accID) references accounts(accID)
);

create table tattlr_accounts
(
accID varchar(20) primary key unique not null,
minDuaration INTEGER,
isDeleted int,
oauth_token_secret varchar(50),
oauthToken varchar(50),
last_tweet varchar(20),
foreign key (accID) references accounts(accID)
);
create table twitter_accounts
(
accID varchar(20) primary key not null,
foreign key (accID) references accounts(accID)
);

create table posts
(
postID varchar(20) primary key not null unique,
accID varchar(20) not null,
userID int,
text varchar(140),
time datetime,
foreign key (userID) references users(userID),
foreign key (accID) references accounts(accID)
);

create table direct_messages
(
postID varchar(20) primary key unique not null,
sentTo varchar(20) not null,
foreign key(postID) references posts(postID)
);
create table events
(
id Integer auto_increment  primary key not null,
name varchar(20),
description text,
creatorID int not null,
accountID varchar(20) not null,
deleted bit,
status varchar(20) default 'incomplete',
foreign key (creatorID) references users(userID),
foreign key (accountID) references accounts(accID)
);
create table tweets
(
postID varchar(20) primary key unique not null,
retweets int default 0,
favourites int default 0,
isMention bit default false,
restored bit  default false,
restoreDate datetime,
subTime datetime,
restoredBy varchar(20),
eventID int,
retweeted_by_us bit default 0,
favorited_by_us bit default 0,
tweetInterval datetime,
replyTo varchar(20),
foreign key(postID) references posts(postID),
foreign key(eventID) references events(id)
);
create table archived_tweets
(
postID varchar(20) primary key unique not null,
retweets int default 0,
favourites int default 0,
isMention bit default false,
subTime datetime,
deletedBy varchar(20),
eventID int,
tweetInterval datetime,
foreign key(postID) references posts(postID),
foreign key (eventID) references events(id)
);


create table future_tweets
(
id Integer auto_increment primary key not null,
userID int not null,
tweetText text,
eventID Integer,
approved int default 0,
release_date datetime,
sub_time datetime,
accID varchar(20),
saved bit default 0,
imageName varchar(100),
reply_to varchar(20),
foreign key(accID) references accounts(accID),
foreign key(eventID) references events(id),
foreign key (userID) references users(userID)
);
create table future_dms
(
id Integer auto_increment primary key not null,
userID int not null,
sentTo varchar(20) not null,
messageText text,
approved bit default 0,
sub_time datetime,
accID varchar(20),
foreign key (accID) references accounts(accID),
foreign key (userID) references users(userID)
);
create table tasks
(
taskDate timestamp default current_timestamp,
taskID Integer auto_increment primary key not null,
sentBy int not null,
accID varchar(20),
status varchar(10) default 'incomplete',
originalMessage text,
postID varchar(20),
foreign key (postID) references tweets(postID),
foreign key (sentBy) references users(userID),
foreign key (accID) references accounts(accID)
);
create table messages
(
messageID integer auto_increment,
messageDate timestamp default current_timestamp,
messageText text,
userID INTEGER  not null,
taskID integer not null,
primary key(messageID),
foreign key(taskID) references tasks(taskID),
foreign key(userID) references users(userID)
);
create table users_favourite_tweets
(
userID int not null,
postID varchar(20) not null,
primary key(userID, postID),
favouriteDate datetime,
foreign key (userID) references users(userID),
foreign key (postID) references posts(postID)
);
create table users_retweet_tweets
(
userID int not null,
postID varchar(20) not null,
primary key(userID, postID),
retweetDate datetime,
foreign key (userID) references users(userID),
foreign key (postID) references posts(postID)
);
create table accounts_has_users
(
accID varchar(20) not null,
userID int not null,
permissionLevel Integer,
primary key(accID, userID),
foreign key(accID) references tattlr_accounts(accID),
foreign key (userID) references users(userID)
);

create table twitterAccounts_retweet_tweets
(
twitterAccountID varchar(20) not null,
postID varchar(20) not null,
primary key(twitterAccountID, postID),
retweetDate datetime,
new_id varchar(20),
foreign key (twitterAccountID) references twitter_accounts(accID),
foreign key (postID) references Tweets(postID)
);
create table twitterAccounts_favourite_tweets
(
twitterAccountID varchar(20) not null,
postID varchar(20) not null,
primary key(twitterAccountID, postID),
favouriteDate datetime,
foreign key (twitterAccountID) references twitter_accounts(accID),
foreign key (postID) references Tweets(postID)
);
create table tasks_has_users
(
taskID Integer not null,
userID Integer not null,
permissionLevel varchar(10),
status varchar(10) default 'pending'
);
INSERT INTO `db`.`users` (`fullname`, `username`, `password`, `admin`) VALUES ('TattlrAdmin', 'adminadmin', 'e700c68b5a3ecb7c84ef22a9bc5a76ddcfe4f4b4', 1);
alter table posts change text text text 
 


");
    }

}

?>
